import express from 'express';
import { body, validationResult } from 'express-validator';
import { executeQuery, getOne, transaction } from '../config/database.js';
import { authenticateToken, requireAdmin, requireTeacher } from '../middleware/auth.js';

const router = express.Router();

// 获取所有班级
router.get('/', authenticateToken, async (req, res) => {
  try {
    const { grade, page = 1, limit = 10 } = req.query;
    const pageNum = parseInt(page) || 1;
    const limitNum = parseInt(limit) || 10;
    const offset = (pageNum - 1) * limitNum;

    let sql = `
      SELECT /*+ NO_CACHE */ 
        c.id, c.name, c.grade, c.description, c.created_at,
        COUNT(DISTINCT s.id) as student_count,
        COALESCE(GROUP_CONCAT(DISTINCT u.name SEPARATOR ', '), '') as teachers
      FROM classes c
      LEFT JOIN students s ON c.id = s.class_id
      LEFT JOIN teacher_classes tc ON c.id = tc.class_id
      LEFT JOIN users u ON tc.teacher_id = u.id AND u.role = 'teacher'
    `;
    let params = [];

    if (grade) {
      sql += ' WHERE c.grade = ?';
      params.push(grade);
    }

    sql += ` GROUP BY c.id, c.name, c.grade, c.description, c.created_at ORDER BY c.grade, c.name LIMIT ${limitNum} OFFSET ${offset}`;

    console.log('Classes Debug - SQL:', sql);
    console.log('Classes Debug - Params:', params);
    console.log('Classes Debug - Params length:', params.length);

    const classes = await executeQuery(sql, params);

    // 获取总数
    let countSql = 'SELECT COUNT(*) as total FROM classes c';
    let countParams = [];
    
    if (grade) {
      countSql += ' WHERE c.grade = ?';
      countParams.push(grade);
    }
    
    const totalResult = await getOne(countSql, countParams);

    res.json({
      success: true,
      data: {
        classes,
        pagination: {
          page: pageNum,
          limit: limitNum,
          total: totalResult.total,
          pages: Math.ceil(totalResult.total / limitNum)
        }
      }
    });

  } catch (error) {
    console.error('获取班级列表错误:', error);
    res.status(500).json({
      success: false,
      message: '服务器错误'
    });
  }
});

// 获取班级统计信息（仪表盘用）
router.get('/statistics', authenticateToken, async (req, res) => {
  try {
    const { date = new Date().toISOString().split('T')[0] } = req.query;

    const statistics = await executeQuery(`
      SELECT 
        c.id,
        c.name,
        c.grade,
        COUNT(DISTINCT s.id) as total_students,
        COUNT(CASE WHEN ar.arrival_status = 'present' AND ar.attendance_date = ? THEN 1 END) as present_count,
        COUNT(CASE WHEN ar.arrival_status = 'leave' AND ar.attendance_date = ? THEN 1 END) as leave_count,
        COUNT(CASE WHEN ar.arrival_status = 'abnormal' AND ar.attendance_date = ? THEN 1 END) as abnormal_count,
        ROUND(
          COUNT(CASE WHEN ar.arrival_status = 'present' AND ar.attendance_date = ? THEN 1 END) * 100.0 / 
          NULLIF(COUNT(DISTINCT s.id), 0), 2
        ) as attendance_rate
      FROM classes c
      LEFT JOIN students s ON c.id = s.class_id
      LEFT JOIN attendance_records ar ON s.id = ar.student_id
      GROUP BY c.id, c.name, c.grade
      ORDER BY c.grade, c.name
    `, [date, date, date, date]);

    res.json({
      success: true,
      data: statistics
    });

  } catch (error) {
    console.error('获取班级统计错误:', error);
    res.status(500).json({
      success: false,
      message: '服务器错误'
    });
  }
});

// 获取单个班级详情
router.get('/:id', authenticateToken, async (req, res) => {
  try {
    const classId = req.params.id;

    // 获取班级基本信息
    const classInfo = await getOne(`
      SELECT id, name, grade, description, created_at 
      FROM classes 
      WHERE id = ?
    `, [classId]);

    if (!classInfo) {
      return res.status(404).json({
        success: false,
        message: '班级不存在'
      });
    }

    // 获取班级教师
    const teachers = await executeQuery(`
      SELECT u.id, u.name, u.phone
      FROM users u
      INNER JOIN teacher_classes tc ON u.id = tc.teacher_id
      WHERE tc.class_id = ? AND u.role = 'teacher'
    `, [classId]);

    // 获取班级学生
    const students = await executeQuery(`
      SELECT id, student_no, name, qr_code
      FROM students 
      WHERE class_id = ?
      ORDER BY student_no
    `, [classId]);

    res.json({
      success: true,
      data: {
        ...classInfo,
        teachers,
        students,
        student_count: students.length
      }
    });

  } catch (error) {
    console.error('获取班级详情错误:', error);
    res.status(500).json({
      success: false,
      message: '服务器错误'
    });
  }
});

// 创建班级（仅管理员）
router.post('/', [
  authenticateToken,
  requireAdmin,
  body('name').trim().notEmpty().withMessage('班级名称不能为空'),
  body('grade').trim().notEmpty().withMessage('年级不能为空'),
  body('description').optional().trim()
], async (req, res) => {
  try {
    // 验证输入
    const errors = validationResult(req);
    if (!errors.isEmpty()) {
      return res.status(400).json({
        success: false,
        message: '输入验证失败',
        errors: errors.array()
      });
    }

    const { name, grade, description } = req.body;

    // 检查班级名是否已存在
    const existingClass = await getOne(
      'SELECT id FROM classes WHERE name = ?',
      [name]
    );

    if (existingClass) {
      return res.status(400).json({
        success: false,
        message: '班级名称已存在'
      });
    }

    // 创建班级
    const result = await executeQuery(`
      INSERT INTO classes (name, grade, description) 
      VALUES (?, ?, ?)
    `, [name, grade, description]);

    res.status(201).json({
      success: true,
      message: '班级创建成功',
      data: {
        id: result.insertId,
        name,
        grade,
        description
      }
    });

  } catch (error) {
    console.error('创建班级错误:', error);
    res.status(500).json({
      success: false,
      message: '服务器错误'
    });
  }
});

// 分配教师到班级（仅管理员）
router.post('/:id/teachers', [
  authenticateToken,
  requireAdmin,
  body('teacherIds').isArray().withMessage('教师ID必须是数组')
], async (req, res) => {
  try {
    // 验证输入
    const errors = validationResult(req);
    if (!errors.isEmpty()) {
      return res.status(400).json({
        success: false,
        message: '输入验证失败',
        errors: errors.array()
      });
    }

    const classId = req.params.id;
    const { teacherIds } = req.body;

    // 检查班级是否存在
    const classExists = await getOne('SELECT id FROM classes WHERE id = ?', [classId]);
    if (!classExists) {
      return res.status(404).json({
        success: false,
        message: '班级不存在'
      });
    }

    // 检查要分配的教师是否已经被分配到其他班级
    if (teacherIds.length > 0) {
      const existingAssignments = await executeQuery(`
        SELECT tc.teacher_id, tc.class_id, u.name as teacher_name, c.name as class_name
        FROM teacher_classes tc
        JOIN users u ON tc.teacher_id = u.id
        JOIN classes c ON tc.class_id = c.id
        WHERE tc.teacher_id IN (${teacherIds.map(() => '?').join(', ')}) 
        AND tc.class_id != ?
      `, [...teacherIds, classId]);
      
      if (existingAssignments.length > 0) {
        const conflicts = existingAssignments.map(assignment => 
          `${assignment.teacher_name} 已分配到 ${assignment.class_name}`
        ).join(', ');
        
        return res.status(400).json({
          success: false,
          message: `教师分配冲突: ${conflicts}。每个教师只能分配到一个班级。`
        });
      }
    }

    // 使用事务处理
    await transaction(async (connection) => {
      // 删除现有的教师分配
      await connection.execute(
        'DELETE FROM teacher_classes WHERE class_id = ?',
        [classId]
      );

      // 添加新的教师分配
      if (teacherIds.length > 0) {
        const values = teacherIds.map(teacherId => [teacherId, classId]);
        const placeholders = values.map(() => '(?, ?)').join(', ');
        const flatValues = values.flat();
        await connection.execute(
          `INSERT INTO teacher_classes (teacher_id, class_id) VALUES ${placeholders}`,
          flatValues
        );
      }
    });

    // 添加短暂延迟确保数据库更新完成
    await new Promise(resolve => setTimeout(resolve, 100));
    
    res.json({
      success: true,
      message: '教师分配成功'
    });

  } catch (error) {
    console.error('分配教师错误:', error);
    res.status(500).json({
      success: false,
      message: '服务器错误'
    });
  }
});

// 更新班级信息（仅管理员）
router.put('/:id', [
  authenticateToken,
  requireAdmin,
  body('name').optional().trim().notEmpty().withMessage('班级名称不能为空'),
  body('grade').optional().trim().notEmpty().withMessage('年级不能为空'),
  body('description').optional().trim()
], async (req, res) => {
  try {
    // 验证输入
    const errors = validationResult(req);
    if (!errors.isEmpty()) {
      return res.status(400).json({
        success: false,
        message: '输入验证失败',
        errors: errors.array()
      });
    }

    const classId = req.params.id;
    const { name, grade, description } = req.body;

    // 检查班级是否存在
    const classExists = await getOne('SELECT id FROM classes WHERE id = ?', [classId]);
    if (!classExists) {
      return res.status(404).json({
        success: false,
        message: '班级不存在'
      });
    }

    // 构建更新字段
    const updateFields = [];
    const updateValues = [];

    if (name) {
      updateFields.push('name = ?');
      updateValues.push(name);
    }
    if (grade) {
      updateFields.push('grade = ?');
      updateValues.push(grade);
    }
    if (description !== undefined) {
      updateFields.push('description = ?');
      updateValues.push(description);
    }

    if (updateFields.length === 0) {
      return res.status(400).json({
        success: false,
        message: '没有提供要更新的字段'
      });
    }

    updateFields.push('updated_at = CURRENT_TIMESTAMP');
    updateValues.push(classId);

    await executeQuery(`
      UPDATE classes 
      SET ${updateFields.join(', ')} 
      WHERE id = ?
    `, updateValues);

    res.json({
      success: true,
      message: '班级信息更新成功'
    });

  } catch (error) {
    console.error('更新班级错误:', error);
    res.status(500).json({
      success: false,
      message: '服务器错误'
    });
  }
});

// 删除班级（仅管理员）
router.delete('/:id', authenticateToken, requireAdmin, async (req, res) => {
  try {
    const classId = req.params.id;

    // 检查班级是否存在
    const classExists = await getOne('SELECT id FROM classes WHERE id = ?', [classId]);
    if (!classExists) {
      return res.status(404).json({
        success: false,
        message: '班级不存在'
      });
    }

    // 检查是否有学生
    const studentCount = await getOne(
      'SELECT COUNT(*) as count FROM students WHERE class_id = ?',
      [classId]
    );

    if (studentCount.count > 0) {
      return res.status(400).json({
        success: false,
        message: '班级中还有学生，无法删除'
      });
    }

    await executeQuery('DELETE FROM classes WHERE id = ?', [classId]);

    res.json({
      success: true,
      message: '班级删除成功'
    });

  } catch (error) {
    console.error('删除班级错误:', error);
    res.status(500).json({
      success: false,
      message: '服务器错误'
    });
  }
});

export default router;
